<?php
/*
 * 收货方法
 */
class comm{
	//根据采购单的ID查询采购表
	public static function get_purchaseorders_status($purchaseorder)
	{
		 $db=DB::select()->from('purchaseorders')->where('id','=',$purchaseorder)->execute('newerp')->current();
		 //->current()
		 return  $db;
	}

	//根据采购单ID查询采购明细表得到记录
	public static function getPurchaseorderDetails($purchaseorder)
	{
		return	$orms=DB::select()->from('purchaseorder_details')
		->where('purchaseorder_id','=',$purchaseorder)
			->where('status','<',100)
			->where('status','>=',20)
		->execute('newerp')->as_array();
	} 
	//根据采购ID,SKU查询收货明细单中某个SKU的已经到货的数量,并返回已到货数量
	public static function get_total_arrival_quantuty($purchaseorder,$sku)
	{
//		$sql="select SUM(recieve_details.arrival_quantity) total from  recieve_invoices ".
//		" join recieve_details on recieve_invoices.id = recieve_details.recieve_invoice_id ".
//		 " where recieve_invoices.purchaseorder_id= $purchaseorder ".
//		 " and recieve_details.sku='".$sku."'";
//		
//			$r=DB::query(Database::SELECT, $sql)->execute('newerp')->current();
			
			$r=DB::select(
			array(DB::expr('SUM(recieve_details.arrival_quantity)'),'total_arrival_quantuty'))
			->from('recieve_invoices')
			->join('recieve_details')
			->on('recieve_invoices.id','=','recieve_details.recieve_invoice_id')
			
			->where('recieve_invoices.purchaseorder_id','=',$purchaseorder)
			->where('recieve_details.sku','=',$sku)
		//	->group_by("recieve_details.sku")
			->execute('newerp')->current();
			return $r['total_arrival_quantuty'];
	}
	public static function insert_recieveinvoices($table,$array)
	{
		$db=DB::insert($table,array_keys($array))->values($array);		
		$db=$db->execute('newerp');
		return $db;
	}
	//得到添加SKU到收货明细里有多少次
	public static function sku_count($sku,$purchaseorder)
	{
		$result=DB::select(
		'recieve_details.sku'					
		)
		->from('recieve_invoices')
		->join('recieve_details')
		->on('recieve_invoices.id','=','recieve_details.recieve_invoice_id')				
		->where('recieve_invoices.purchaseorder_id','=',$purchaseorder)
		->where("recieve_details.sku",'=',$sku)					
		->execute('newerp')->as_array();
		return count($result);
	}
	//更新采购明细表的状态
	public static function update_status($sku,$purchaseorder,$purchaseorder_status)
	{
		$update_purchaseorder_details=DB::update('purchaseorder_details')
		->set(array('status'=>$purchaseorder_status))
		->where('purchaseorder_id','=',$purchaseorder)
		->where('sku','=',$sku)
		->execute('newerp');
		return $update_purchaseorder_details;
	}
	//更新采购明细表的到货数量
	public static function update_total_arrival_quantuty($sku,$purchaseorder,$total_arrival_quantuty)
	{
//		$sql="update purchaseorder_details set recieve_quantity = $total_arrival_quantuty".
//		 " where purchaseorder_id = $purchaseorder and sku='".$sku."'";
//		echo $sql;
//		$update_purchaseorder_details=DB::query(Database::UPDATE, $sql)->execute('newerp');
		$update_purchaseorder_details=DB::update('purchaseorder_details')
		->set(array('recieve_quantity'=>$total_arrival_quantuty))
		->where('purchaseorder_id','=',$purchaseorder)
		->where('sku','=',$sku)
		->execute('newerp');
		return $update_purchaseorder_details;
	}
	//修改到货数量的时候，更新采购明细的总到货数量
	public static function update_arrival_quantity_to_purchaseorder_details($sku,$purchaseorder,$total_arrival_quantuty,$qt='+')
	{
		if($total_arrival_quantuty>0)
		{
			$sql="update purchaseorder_details set `recieve_quantity` =`recieve_quantity` $qt $total_arrival_quantuty".
		 " where purchaseorder_id = $purchaseorder and sku='".$sku."'";
		}
		else 
		{			
			$sql="update purchaseorder_details set `recieve_quantity` =`recieve_quantity`  $total_arrival_quantuty".
		 " where purchaseorder_id = $purchaseorder and sku='".$sku."'";	
		}
		
		//echo $sql;
		$update_purchaseorder_details=DB::query(Database::UPDATE, $sql)->execute('newerp');
//		$update_purchaseorder_details=DB::update('purchaseorder_details')
//		->set(array('recieve_quantity'=>$total_arrival_quantuty))
//		->where('purchaseorder_id','=',$purchaseorder)
//		->where('sku','=',$sku)
//		->execute('newerp');
		return $update_purchaseorder_details;
	}
	//根据采购单号查询出收货明细
    public static function select_recieve_details($purchaseorder)
    {
        $r=DB::select()
            ->from('recieve_invoices')
            ->join('recieve_details')
            ->on('recieve_invoices.id','=','recieve_details.recieve_invoice_id')
            ->where('recieve_invoices.purchaseorder_id','=',$purchaseorder)
            //	->group_by("recieve_details.sku")
            ->execute('newerp')->as_array();
        return $r;
    }
	//更新收货明细表的到货数量
	public static function update_arrival_quantuty($id,$update_count)
	{
		$r=DB::update('recieve_details')
		->set(array('arrival_quantity'=>$update_count))
		->where('id','=',$id)
		->execute('newerp');
		return $r;
	}
	//根据SKU查找出采购单号array("purchaseorders.id","id"),array("purchaseorders.supplier","")
	public static function select_erp_provider($name)
	{
		 //$suppliers = DB::select('')->from('erp_provider')->where('vProviderName','like','%'.$name.'%')->limit(15)->execute('readonlyerp')->as_array();
 
	}
	
	/*
	 * 根据用户id获取用户昵称
	 */
	
	public static function getNickByUserId($userId){
	    $sql = 'SELECT nick FROM users WHERE id='.$userId;
	    $nick = DB::query(Database::SELECT,$sql)->execute('newerp')->get('nick');
	    return $nick;
	}
	/*
	 * 根据sku获取跟单员的昵称
	 */
	public static function getNickBySku($sku){
	    $sql = "SELECT users.nick
                FROM skusystem_user_skus
                LEFT JOIN users ON skusystem_user_skus.`user_id`=users.`id`
                WHERE skusystem_user_skus.`sku` = '".$sku."' AND skusystem_user_skus.isavailable=1
                AND skusystem_user_skus.buyertype=2 ORDER BY skusystem_user_skus.modifytime DESC limit 1";
	    $nick = DB::query(Database::SELECT,$sql)->execute('newerp')->get('nick');
	    return $nick;
	}
	/*
	 * 根据预约单查询跟单员的昵称
	 */
	public static function getNickByPurchaseId($purchaseId,$sku){
	    $sql = "SELECT merchandiser
                FROM purchaseorder_details
                WHERE `purchaseorder_id` = ".$purchaseId." AND `sku`='".$sku."'
                ORDER BY details_date DESC limit 1";
	    $nick = DB::query(Database::SELECT,$sql)->execute('newerp')->get('merchandiser');
	    return $nick;
	}
	/*
	 * 根据收货明细id判断是否质检
	 */
	
	public static function getCountOfCheck($reId){
	    $sql = 'SELECT count(1) as total FROM check_quality_details WHERE recieve_detail_id='.$reId;
	    $total = DB::query(Database::SELECT,$sql)->execute('newerp')->get('total');
	    return $total;
	}
}